package cn.edu.gues.infosystam.mapper;

import cn.edu.gues.infosystam.entity.Student;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author 苏小小
 * @since 2020-06-11
 */
public interface StudentMapper extends BaseMapper<Student> {

    /**
     * 获取学生基本信息
     * @param sno 学号
     * @return
     */
    @Select("SELECT student.sno, student.sname, cclass.cname, cclass.college FROM student LEFT JOIN cclass ON student.cid = cclass.cid WHERE student.deleted = 0 and student.sno = #{sno};")
    public Student getStudent(String sno);

    /**
     * 无条件查询所有学生
     * @return
     */
    @Select("SELECT student.sno, student.sname, cclass.cname, cclass.college, student.gmt_modified FROM student LEFT JOIN cclass ON student.cid = cclass.cid WHERE student.deleted = 0 ")
    public IPage<Student> getStudents(Page<?> page);

    /**
     * 根据学院、班级查询学生 封装为Page对象
     * @param page Page对象
     * @param college 学院
     * @param classes 班级
     * @return
     */
    @Select("SELECT student.sid, student.sno, student.spassword, student.sname, student.cid, student.deleted, student.gmt_create, student.gmt_modified, cclass.cname as cname, cclass.college, report.report_id, report.task_id " +
            " FROM student LEFT JOIN report on report.sno = student.sno AND report.task_id = #{taskId} LEFT JOIN cclass ON student.cid = cclass.cid WHERE student.deleted = 0 and student.cid IN ( SELECT cclass.cid FROM cclass WHERE cclass.cname REGEXP #{classes} OR cclass.college REGEXP #{college} )")
    public IPage<Student> getReportStudents(Page<?> page, @Param(value = "taskId") Integer taskId, @Param(value = "college") String college, @Param(value = "classes") String classes);

    @Select("SELECT student.sid, student.sno, student.spassword, student.sname, student.cid, student.deleted, student.gmt_create, student.gmt_modified, cclass.cname as cname, cclass.college, report.report_id, report.task_id " +
            " FROM student LEFT JOIN report on report.sno = student.sno AND report.task_id = #{taskId} LEFT JOIN cclass ON student.cid = cclass.cid WHERE student.deleted = 0 and student.cid IN ( SELECT cclass.cid FROM cclass WHERE cclass.deleted = 0 )")
    public IPage<Student> getReportTaskStudents(Page<?> page, @Param(value = "taskId") Integer taskId);

    @Select("SELECT count(1) as c FROM student LEFT JOIN report on report.sno = student.sno AND " +
            "report.task_id = #{taskId} LEFT JOIN cclass ON student.cid = cclass.cid WHERE student.deleted = 0 and student.cid IN " +
            "( SELECT cclass.cid FROM cclass WHERE cclass.cname REGEXP #{classes} OR cclass.college REGEXP #{college} )")
    public Integer getReportTaskStudentsTotal( @Param(value = "taskId") Integer taskId, @Param(value = "college") String college, @Param(value = "classes") String classes);

}
